package daoClass;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import utils.JDBCDog;
import voClass.DogVO;

public class DogDAO {
	private static final String insertSQL = "insert into dog(id, dname, sex, type, age, regdate, collar, fno, specificloc, storeddept, etc, mercykilling) values (id.nextval, ?, ?, ?, ?, sysdate, ?, ?, ?, ?, ?, ?)";
	private static final String updateSQL = " UPDATE DOG SET DNAME = ?, STOREDDEPT = ?, ETC = ?, MERCYKILLING = ? WHERE ID = ? ";
	private static final String deleteSQL = " DELETE FROM DOG WHERE ID = ? ";
	private static final String selectSQL = " select * from dog ";
	private static final String selectOneSQL = " SELECT * FROM DOG WHERE ID = ? ";
	
	// 1. 회원 가입
	public int insertDog(DogVO dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			conn = JDBCDog.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, dg.getDname());
				ps.setString(2, dg.getSex());
				ps.setString(3, dg.getType());
				ps.setString(4, dg.getAge());
				ps.setString(5, dg.getCollar());
				ps.setString(6, dg.getFno());
				ps.setString(7, dg.getSpecificloc());
				ps.setString(8, dg.getStoreddept());
				ps.setString(9, dg.getEtc());
				ps.setString(10, dg.getMercykilling());
			
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			JDBCDog.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 2. 회원 수정
	public int updateDog(DogVO dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			conn = JDBCDog.openConn();
			ps = conn.prepareStatement(updateSQL);
			
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			JDBCDog.closeConn(conn, ps, null);
		}
		
		return cnt;
		
	}
	// 3. 회원 탈퇴
	public int deleteDog(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			conn = JDBCDog.openConn();
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			JDBCDog.closeConn(conn, ps, null);
		}
		return cnt;
		
	}
	// 4. 회원 전체 조회
	public ArrayList<DogVO> selectDog() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<DogVO> listd = new ArrayList<>();
		try {
			conn = JDBCDog.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while (rs.next()) {
				DogVO dg = new DogVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12));
				listd.add(dg);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			JDBCDog.closeConn(conn, ps, rs);
		}
		return listd;
	}
	// 5. 회원 상세 조회

}




